package useBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import JDBC.JDBC;
import JDBC.DBUtil;
import javaBean.Message;
import org.junit.runner.Request;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class MessageDB {
	
	
	
	public static int insert(Message message) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = JDBC.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}

		String query = "INSERT INTO Message (userid, title, context) VALUES (?, ?, ?)";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, message.getuserid());
			ps.setString(2, message.gettitle());
			ps.setString(3, message.getcontext());
			
			return ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			return 0;
		} finally {
			DBUtil.closePreparedStatement(ps);
			connection.close();
		}
		
	}

	
	public static Message selectMessage(String userid) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = JDBC.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}

		
		String query = "SELECT * FROM Message "
		+ "WHERE userid = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, userid);
			rs = ps.executeQuery();
			Message message = null;
			if (rs.next()) {
				message = new Message ();
				message.setmid(rs.getString("mid"));
				message.setuserid(rs.getString("userid"));
				message.settitle(rs.getString("title"));
				message.setcontext(rs.getString("context"));
			}
			
			return message;
		} catch (SQLException e) {
			System.out.println(e);
			return null;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			connection.close();
		}
	}
	
	public static Message selectMessagem(String mid) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = JDBC.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}

		String query = "SELECT * FROM message  WHERE mid = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, mid);
			rs = ps.executeQuery();
			Message message = null;
			if (rs.next()) {
				message = new Message ();
				message.setmid(rs.getString("mid"));
				message.setuserid(rs.getString("userid"));
				message.settitle(rs.getString("title"));
				message.setcontext(rs.getString("context"));
			}
			
			return message;
		} catch (SQLException e) {
			System.out.println(e);
			return null;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			connection.close();
		}
	}

	public static boolean deleteMessagem(String mid) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = JDBC.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}

		String query = "DELETE  FROM message  WHERE mid = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, mid);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			connection.close();
		}
	}

	public static List<Message> selectMessageByUname(String name) throws SQLException {
		List<Message> mL= new ArrayList<Message>();
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String uid  ="" ;

		try {
			connection = JDBC.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		String query = "SELECT * FROM user  WHERE user_name = ?";
		try{
			ps = connection.prepareStatement(query);
			ps.setString(1, name);
			rs = ps.executeQuery();


			if (rs.next()) {
				uid = Integer.toString(rs.getInt("id")) ;
			}else
			{
				return null ;
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}

		query = "SELECT * FROM message  WHERE userid = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, uid);
			rs = ps.executeQuery();
			Message message = null;

			while (rs.next()) {
				message = new Message ();
				message.setmid(rs.getString("mid"));
				message.setuserid(rs.getString("userid"));
				message.settitle(rs.getString("title"));
				message.setcontext(rs.getString("context"));
				mL.add(message) ;
			}
			return mL;
		} catch (SQLException e) {
			System.out.println(e);
			return null;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			connection.close();
		}
	}

	public static boolean modifyMessagem(String mid , String title , String text  ) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			connection = JDBC.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}

		String query = "Update message set title=? , context=?  where mid = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1,title);
			ps.setString(2,text);
			ps.setString(3, mid);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			connection.close();
		}
	}
	
}
